import mysql.connector
from mysql.connector import Error
from flask import Flask, request, jsonify
import os

app = Flask(__name__)

@app.before_request
def log_request_info():
    print(f"请求方法和URL: {request.method} {request.url}")
    print(f"请求头信息: {dict(request.headers)}")
    print(f"请求数据: {request.get_data().decode('utf-8')}")

@app.after_request
def log_response_info(response):
    print(f"响应状态码: {response.status_code}")
    print(f"响应数据: {response.get_data().decode('utf-8')}")
    return response

@app.route('/query', methods=['POST'])
def execute_sql_query():
    # 获取请求中的SQL语句
    sql_query = request.json.get('sql')
    sql_query = sql_query.encode('utf-8')
    if not sql_query:
        return jsonify({"error": "必须提供SQL查询语句"}), 400

    try:
        # 从环境变量获取数据库配置
        db_config = {
            'host': os.getenv('MYSQL_HOST', '127.0.0.1'),
            'database': os.getenv('MYSQL_DATABASE', 'db2025'),
            'user': os.getenv('MYSQL_USER', 'root'),
            'password': os.getenv('MYSQL_PASSWORD', 'ning1991')
        }
        
        # 建立数据库连接
        connection = mysql.connector.connect(**db_config)
        cursor = connection.cursor(dictionary=True)
        
        # 执行查询
        cursor.execute(sql_query)
        results = cursor.fetchall()
        
        # 格式化结果
        formatted_results = []
        for row in results:
            # 处理None值和格式化数字
            formatted_row = {}
            for key, value in row.items():
                if value is None:
                    formatted_row[key] = None
                elif isinstance(value, (int, float)):
                    formatted_row[key] = value
                else:
                    formatted_row[key] = str(value)
            formatted_results.append(formatted_row)
        return jsonify(formatted_results)
        
    except Exception as error:
        app.logger.error(f"数据库操作错误: {str(error)}")
        return jsonify({"error": f"查询执行失败: {str(error)}"}), 500
        
    finally:
        if 'cursor' in locals():
            cursor.close()
        if 'connection' in locals():
            connection.close()

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5000)
